The query interface is divided into three distinct sections called the Filter, Selection and Output pane. The Selection list is only populated with fields and folders after the Table lookup has been set with a query object.
Field items in the Selection pane can be double clicked and they will be added to the Output pane. Each pane supports drag and drop, so Selection fields can be dragged into the Output and Filter pane. Alternatively, the Filter items below the Table lookup can be dragged into the Output pane.
The Query Mode, Product and Table lookups have special significance and are explained in subsequent paragraphs. Some DataModels also have a special over-riding lookup called a Superfield which can interact directly with the DataModel control logic and adjust its behaviour on the fly. A Superfield can have any description, for example Company, Database or Entity but it always falls between the Product and the Table lookup.
Note: The far right Details column for the Mode and Product filters display the current user login and current DataModel version.
The filter pane controls the type of query generated, the source of the query and what query object will be queried. Additional filters after the Table lookup refine the query scope to only return the records that are relevant.
The Mode filter in the Query Builder defines the structure of the generated query. Each mode alters the basic premise for the query and will return slightly different information. Before constructing a query make sure the correct Mode has been selected in the Filter pane, it will avoid confusion when the result set is different from what was expected.
In most cases, the Mode can be changed on an existing query and all the user will need do is update the Aggregations for the number columns.
| Item | Description |
|---|---|
|
Aggregations |
Returns a single aggregation. For example, the output column is summed, or counted, or average, etc. |
|
Summary Report |
Returns grouped elements where some columns are aggregated whilst others are grouped on their values. |
|
Detailed Report |
Returns a transactional row by row list of all the records available under the current filter criteria. |
|
References |
Returns a single row of referential data. |
|
Lookups |
Returns field lookup data related to the filter. |
The selection of a DataModel for querying is referred to as selecting a Product in the Query Builder. The available DataModels can be displayed by opening the Products dialog. Depending on the configuration of the Client Setup, there may be Local and Remote DataModels. Selecting a Product (DataModel) will then mean that all subsequent queries constructed against that Product will reference back to its connection details and query service. If a connection is no longer valid or is re-mapped to another connection alias; then the queries will switch allegiances to the next available connection, starting with the Local Connections and then trying the Remote Connections.
A Superfield is a special filter that can control the operation of a DataModel and adjust its behaviour on the fly. The use of a Superfield is at the discretion of the DataModel author but once it is defined, it will be displayed between the Product and Table filters in the Query Builder. A simple example of a Superfield could be the database, where an application has multiple databases to store company or organisation specific information. In this case, the selection of the Superfield will decide which database is queried from. Complex business logic can be tied to a Superfield so when it changes, additional security can be enforced and/ or fields and tables might be disabled from view in the Selection list.
]
The Table tree is a visual structure of query objects grouped into logic folders. The selection of a Table will create the focus for the query design because the Table represents all the available fields and related information that has been defined in the DataModel. Often the Table dialog will have a folder structure that mimics or is closely aligned to the underlying application and its menu.
The structure of the Table tree is controlled by the DataModel author, however end-users can suppress the display of folders and tables using the Restrict Access to Tables option in Site Setup.
